// .........................................................................
// Title: portfolio_composition_sumstats.do
//
// Generates further sample summary statistics, examining additional details
// on the characteristics of the portfolios held by both insurers and 
// investment funds
// .........................................................................

* -------------------------------------------
* Collapse insurer holdings to security level
* -------------------------------------------

* add security masterfile info
use "$insurance_holdings/all_insurance_master", clear
mmerge cusip using "$raw/cmns/gcap_security_master_cusip.dta", unmatched(m)

* collapse to security level
gcollapse (sum) carrying_value conditional_fair_value actual_cost aggregate_par_value marketvalue_usd, ///
    by(cusip date_q asset_class asset_issuer_type asset_type)
mmerge cusip using "$raw/cmns/gcap_security_master_cusip.dta", unmatched(m)
drop _merge
format %tq date_q

* yearly frequency
gen quarter = quarter(dofq(date_q))
keep if quarter == 4
gen year = year(dofq(date_q))
drop date_q
save "$tmp/all_insurance_master_full_timeseries_secm_scollapse_y", replace

* ------------------------------------------
* Portfolio characteristics: insurers
* ------------------------------------------

* Bond ratings
use "$tmp/all_insurance_master_full_timeseries_secm_scollapse_y", clear
drop if missing(cusip)
mmerge cusip using "$raw/cmns/gcap_security_master_cusip.dta", unmatched(m) ukeep(class_code2)
keep if class_code2 == "BC"
mmerge cusip year using "$tmp/issue_ratings_merged_sp_moodys_y", unmatched(m) ukeep(rating*)
keep if _merge == 3
drop if missing(rating_cat)
gen ig_cat = "IG" if inlist(rating_cat, "AAA", "AA", "A", "BBB")
replace ig_cat = "HY" if ~inlist(rating_cat, "AAA", "AA", "A", "BBB")

* High-yield share
gcollapse (sum) marketvalue_usd, by(year ig_cat)
by year: egen totVal = total(marketvalue_usd)
gen share = marketvalue_usd / totVal
keep if ig_cat == "IG"
gen hy_share = round((1 - share) * 100)
keep year hy_share
save "$tmp/insurer_chars_hy", replace

* Merge holdings and other characteristics
use "$tmp/all_insurance_master_full_timeseries_secm_scollapse_y", clear
drop if missing(cusip)
mmerge cusip using "$raw/cmns/gcap_security_master_cusip.dta", unmatched(m) ukeep(class_code2)
keep if class_code2 == "BC"
keep if inlist(year, 2005, 2010, 2015, 2020)
mmerge cusip using "$tmp/ciq_static_characteristics_processed", unmatched(m)
mmerge cusip using "$tmp/consolidated_static_dummies", unmatched(m) ukeep(bc_is_senior bc_is_floating bc_is_callable) update
mmerge cusip year using "$tmp/amounts_outstanding_y_mv", unmatched(m)
mmerge cusip year using "$tmp/duration_merged_y", unmatched(m)
drop _merge
save "$tmp/insurer_holdings_underlying_data_xt", replace 

* Averages for dummies and amounts
use "$tmp/insurer_holdings_underlying_data_xt", clear
keep if class_code2 == "BC"
drop if marketvalue_usd < 0
gcollapse (mean) bc_is_senior bc_is_floating bc_is_callable value_outstanding [aw=marketvalue_usd], by(year)
gen bc_junior = 1 - bc_is_senior
foreach var of varlist bc* {
    replace `var' = round(`var' * 100)
}
replace value_outstanding = round(value_outstanding)
keep year bc_junior bc_is_floating bc_is_callable value_outstanding
order year bc_junior bc_is_floating bc_is_callable value_outstanding
rename bc_* *
rename is_* *
save "$tmp/insurer_chars_dummies_values", replace

* Average duration
use "$tmp/insurer_holdings_underlying_data_xt", clear
drop if missing(duration_y)
drop if  marketvalue_usd < 0 | duration_y < 0
gcollapse (mean) duration_y [aw=marketvalue_usd], by(year)
replace duration_y = round(duration, .1)
keep year duration_y
rename duration_y avg_duration
save "$tmp/insurer_char_drn", replace

* Append portfolio characteristic sumstats for insurers
use "$tmp/insurer_chars_hy", clear
mmerge year using "$tmp/insurer_chars_dummies_values"
mmerge year using "$tmp/insurer_char_drn"
drop _merge
keep if inlist(year, 2005, 2010, 2015, 2020)
rename value_outstanding value
order year avg_duration hy_share junior floating callable value
gen panel = "A. US Insurers"
order panel year avg_duration value hy_share junior floating callable
save "$tmp/portfolio_characteristics_insurers", replace

* --------------------------------------------------
* Portfolio characteristics: specialist funds
* --------------------------------------------------

* specialist funds: collapse holdings to us/foreign level
clear
gen year = .
foreach year in 2005 2010 2015 2020 {
    append using "$tmp/specialist_fund_holdings_`year'"
    replace year = `year' if missing(year)
}
drop if missing(cusip)
replace DomicileCountryId = "Foreign" if DomicileCountryId != "USA"
gcollapse (sum) marketvalue_usd, by(cusip DomicileCountryId year)
mmerge cusip using "$raw/cmns/gcap_security_master_cusip.dta", unmatched(m)
drop _merge
keep if inlist(class_code1, "E", "B")
rename DomicileCountryId region
replace region = lower(region)
drop if marketvalue_usd < 0
save "$tmp/specialist_fund_holdings_collapsed", replace

* Ratings
use "$tmp/specialist_fund_holdings_collapsed", clear
keep if class_code2 == "BC"
mmerge cusip year using "$tmp/issue_ratings_merged_sp_moodys_y", unmatched(m) ukeep(rating*)
keep if _merge == 3
drop if missing(rating_cat)
gen ig_cat = "IG" if inlist(rating_cat, "AAA", "AA", "A", "BBB")
replace ig_cat = "HY" if ~inlist(rating_cat, "AAA", "AA", "A", "BBB")

* High-yield share
gcollapse (sum) marketvalue_usd, by(year region ig_cat)
by year region: egen totVal = total(marketvalue_usd)
gen share = marketvalue_usd / totVal
keep if ig_cat == "IG"
gen hy_share = round((1 - share) * 100)
keep year region hy_share
order region year hy_share
sort region year
save "$tmp/fund_chars_hy", replace

* Merge holdings and other characteristics
use "$tmp/specialist_fund_holdings_collapsed", clear
keep if class_code2 == "BC"
mmerge cusip using "$tmp/ciq_static_characteristics_processed", unmatched(m)
mmerge cusip year using "$tmp/amounts_outstanding_y_mv", unmatched(m)
mmerge cusip year using "$tmp/duration_merged_y", unmatched(m)
drop _merge
save "$tmp/specialist_fund_holdings_collapsed_xt", replace    

* Average duration
use "$tmp/specialist_fund_holdings_collapsed_xt", clear
drop if missing(duration_y)
drop if marketvalue_usd < 0 | duration_y < 0
gcollapse (mean) duration_y [aw=marketvalue_usd], by(region year)
replace duration = round(duration, .1)
rename duration avg_duration
save "$tmp/fund_chars_drn", replace

* Averages for dummies and amounts
use "$tmp/specialist_fund_holdings_collapsed_xt", clear
drop if marketvalue_usd < 0
gcollapse (mean) bc_is_senior bc_is_floating bc_is_callable value_outstanding [aw=marketvalue_usd], by(region year)
gen bc_junior = 1 - bc_is_senior
foreach var of varlist bc* {
    replace `var' = round(`var' * 100)
}
replace value = round(value)
rename bc_* *
rename is_* *
save "$tmp/fund_chars_dummies_values", replace

* Append portfolio characteristic sumstats for funds
use "$tmp/fund_chars_hy", clear
mmerge region year using "$tmp/fund_chars_drn"
mmerge region year using "$tmp/fund_chars_dummies_values"
drop _merge senior
order region year avg_duration* hy_share junior floating callable value
rename value_outstanding value
gen panel = "B. US Funds" if region == "usa"
replace panel = "C. Non-US Funds" if region == "foreign"
drop region
order panel year avg_duration value hy_share junior floating callable
save "$tmp/portfolio_characteristics_funds", replace

* ----------------------------------------------------------------
* Construct full portfolio characteristics sumstat table (Table 2)
* ----------------------------------------------------------------
clear
append using "$tmp/portfolio_characteristics_insurers"
append using "$tmp/portfolio_characteristics_funds"
sort panel year
rename panel Panel
rename year Year
rename avg_duration Average_Duration
rename value Average_Size
rename hy_share Share_High_Yield
rename junior Share_Junior
rename floating Share_Floating
rename callable Share_Callable
order Panel Year Average_Duration Average_Size Share_High_Yield Share_Junior Share_Floating Share_Callable
save "$tables/portfolio_characteristics_overview.dta", replace
export delimited "$tables/portfolio_characteristics_overview.txt", noquote delimiter(tab) replace
